package io.a.h

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.col

object DataQuery {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder.appName("DataQuery").master("local[*]").getOrCreate()

    // 读取数据
    val studentsDF = spark.read
      .format("jdbc")
      .option("url", "jdbc:mysql://47.98.45.56:3306/sql")
      .option("dbtable", "student")
      .option("user", "root")
      .option("password", "inspection@")
      .option("driver", "com.mysql.cj.jdbc.Driver")
      .load()

    // 查询年龄大于20岁的学生
    val olderThan20 = studentsDF.filter(col("age") > 20)
    olderThan20.show()

    // 按专业和入学日期排序
//    val sortedStudents = studentsDF.orderBy(col("major").asc, col("enrollment_date").asc)
//    sortedStudents.show()

    // 统计各专业学生人数
//    val majorCounts = studentsDF.groupBy("major").count()
//    majorCounts.show()

    spark.stop()
  }
}